package com.shuwei.location.db;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.lang.reflect.ParameterizedType;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

/**
 * Created by Halohoop on 2017/10/24.
 * 一张表一个SDKSQLiteOpenHelper的实现类。1对1
 * @author Halohoop
 */

public abstract class Dao<T> extends SQLiteOpenHelper implements ISWDBHelper<T>,
        TableTypeFilter {

    public final static String DB_FILE_NAME = "shuweisdk";
    public final static String ID = "_id";

    public Dao(Context context, int version) {
        this(context, DB_FILE_NAME, null, version, null);
    }

    private Dao(Context context, String name, SQLiteDatabase.CursorFactory
            factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }

    /**
     * 返回表名
     * @return
     */
    protected String getTableName() {
        return getClass().getSimpleName();
    }

    @Override
    public void onConfigure(SQLiteDatabase db) {
        super.onConfigure(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        super.onDowngrade(db, oldVersion, newVersion);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createTableIfNotExists(db);
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        if (!db.isReadOnly()) {
            db.execSQL("PRAGMA foreign_keys=ON;");
        }
    }

    protected final void createTableIfNotExists(SQLiteDatabase db) {
        String tableSql = createTableSql();
        db.execSQL(tableSql);
    }

    /**
     * 建表
     *
     */
    private String createTableSql() {
//        获取泛型
        Class<? extends Dao> clz = this.getClass();
        ParameterizedType genericSuperclass = (ParameterizedType) clz.getGenericSuperclass();
        Class type = (Class) genericSuperclass.getActualTypeArguments()[0];
        Field fieldId = getFieldId(type);
        //必须要存在一个_id字段
        if (fieldId == null) {
            throw new RuntimeException("没有id字段，请设置id字段！");
        }
        final Id id = fieldId.getAnnotation(Id.class);
        StringBuilder builder = new StringBuilder();
        final String dbName = getTableName();
        if (TextUtils.isEmpty(dbName)) {
            throw new RuntimeException("请在getDbName()方法中返回一个有效的表名!");
        }
        //id主键自增列
        builder.append("CREATE TABLE IF NOT EXISTS " + dbName + "(");
        if (id.autoincrement()) {
            //存在id且自增
            builder.append("_id INTEGER PRIMARY KEY AUTOINCREMENT,");
        } else {
            //存在id不自增
            builder.append("_id INTEGER PRIMARY KEY,");
        }
        final List<String[]> foreignLists = new ArrayList<>(1);
        //新增字段列
        for (Field field : type.getDeclaredFields()) {
            int modifiers = field.getModifiers();
            String fieldName = field.getName();
            if (!ID.equals(fieldName)
                    && !Modifier.isStatic(modifiers)
                    && !"$change".equals(fieldName)//instant run 导致的新变量$change
                    && !"serialVersionUID".equals(fieldName)) {
                builder.append(fieldName).append(" VARCHAR(1000),");
            }
            //判断是否是外键标识
            ForeignKey foreignKey = field.getAnnotation(ForeignKey.class);
            if (foreignKey != null) {
                final String foreignTableName = foreignKey.foreignTableName();
                if (TextUtils.isEmpty(foreignTableName)) {
                    throw new RuntimeException("Please Offer A Table Name For Creating A Foreign Column!");
                }
                //默认是"_id"
                final String foreignTableColumnName = foreignKey.foreignTableColumnName();
                String[] foreignItem = new String[3];
                foreignItem[0] = fieldName;//字段名字
                foreignItem[1] = foreignTableName;//关联的表名
                foreignItem[2] = foreignTableColumnName;//关联的表的字段名
                foreignLists.add(foreignItem);
            }
        }
        //新增外键列
        //sql: FOREIGN KEY(customer_id) REFERENCES customers(id), 卸载create 语句的最下面 最后一个}之上
        for (int i = 0; i < foreignLists.size(); i++) {
            String[] foreignItem = foreignLists.get(i);
//            foreignItem[0]//字段名字
//            foreignItem[1]//关联的表名
//            foreignItem[2]//关联的表的字段名
            builder.append("FOREIGN KEY(").append(foreignItem[0])
                    .append(") REFERENCES ")
                    .append(foreignItem[1]).append("(").append(foreignItem[2]).append("),");
        }
        builder.deleteCharAt(builder.length() - 1);
        builder.append(")");
        //建表语句生成完毕
        final String sql = builder.toString();
        return sql;
    }

    /**
     * 获取对象属性中的id字段，如果有就获取，没有就不获取
     *
     * @param table
     * @return
     */
    public final Field getFieldId(Class table) {
        Field fieldId = null;
        try {
            fieldId = table.getDeclaredField("_id");
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return fieldId;
    }

    /**
     * 工具方法，使用事务
     * 增删改查统一调用这个方法集合的工具类
     *
     * @param exeSql
     */
    protected final void execSQLInternalWithTransaction(String exeSql) {
//        SQLiteDatabase localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(getDbFilePath(),
//                null);
        SQLiteDatabase localSQLiteDatabase = null;
        try {
            localSQLiteDatabase = getWritableDatabase();
            //如果不存在就创建一张表 TODO 优化点，不能每次都判断是否存在
            createTableIfNotExists(localSQLiteDatabase);
            localSQLiteDatabase.beginTransaction();
            localSQLiteDatabase.execSQL(exeSql);
            localSQLiteDatabase.setTransactionSuccessful();
            localSQLiteDatabase.endTransaction();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (localSQLiteDatabase != null) {
                localSQLiteDatabase.close();
            }
        }
    }
    /**
     * 工具方法，不使用事务
     * 增删改查统一调用这个方法集合的工具类
     *
     * @param exeSql
     */
    protected final void execSQLInternalWithoutTransaction(SQLiteDatabase writableDb, String
            exeSql) throws SQLException {
//        SQLiteDatabase localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(getDbFilePath(),
//                null);
        //如果不存在就创建一张表 TODO 优化点，不能每次都判断是否存在
        createTableIfNotExists(writableDb);
        writableDb.execSQL(exeSql);
    }

    /**
     * 工具方法
     * 插入集合的工具类
     *
     * @param ts
     * @param exeSqlWithPlaceholder
     */
    protected final void execSQLDoCollectionInsertInternal(Collection<T> ts, String
            exeSqlWithPlaceholder, Object... args) {
//        SQLiteDatabase localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(getDbFilePath(),
//                null);
        SQLiteDatabase localSQLiteDatabase = null;
        try {
            localSQLiteDatabase = getWritableDatabase();
            localSQLiteDatabase.beginTransaction();
            Iterator<T> iterator = ts.iterator();
            while (iterator.hasNext()) {
                T t = iterator.next();
                String sql = String.format(exeSqlWithPlaceholder, args);
                localSQLiteDatabase.execSQL(sql);
            }
            localSQLiteDatabase.setTransactionSuccessful();
            localSQLiteDatabase.endTransaction();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (localSQLiteDatabase != null) {
                localSQLiteDatabase.close();
            }
        }
    }

    /**
     * 返回的cursor要记得关闭。
     * @param db
     * @param exeSql
     * @param selectionArgs
     * @return
     */
    protected final Cursor rawQueryInternal(SQLiteDatabase db, String exeSql, String[] selectionArgs) {
//        SQLiteDatabase localSQLiteDatabase = SQLiteDatabase.openOrCreateDatabase(getDbFilePath(),
//                null);
        try {
            return db.rawQuery(exeSql, selectionArgs);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 剔除使用{@link Transient}标记的变量
     * @param clz
     * @return
     */
    private Field[] getDeclaredFieldsByFilter(Class<T> clz, boolean needIdField) {
        Field[] declaredFields = clz.getDeclaredFields();
        if (declaredFields==null) {
            return new Field[0];
        }
        final List<Field> fields = new ArrayList<>();
        for (int i = 0; i < declaredFields.length; i++) {
            Field field = declaredFields[i];
            String name = field.getName();
            if (ID.equals(name) && !needIdField) {
                continue;
            }
            Transient annotation = field.getAnnotation(Transient.class);
            if (annotation != null) {
                continue;
            }
            fields.add(field);
        }
        return fields.toArray(new Field[0]);
    }

    //接口的实现
    @Override
    public final void save(T t) {
        Class<T> clz = (Class<T>) t.getClass();
        final Field[] fields = getDeclaredFieldsByFilter(clz, false);
        final String dbName = getTableName();
        if (fields == null) {
            return;
        }
        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO " + dbName + "(");
        StringBuffer sbValueColumns = new StringBuffer();
        //示例：insert into 学生 values(‘xs01‘,‘one‘,‘bj01‘)
        for (int i = 0; i < fields.length; i++) {
            //表字段的sql插入语句
            Field field = fields[i];
            String name = field.getName();
            sb.append(name);
            if (i != (fields.length - 1)) {
                sb.append(",");
            }
            //合成values段的sql插入语句
            field.setAccessible(true);
            try {
                Object o = field.get(t);
                sbValueColumns.append("'" + o + "'");
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            if (i != (fields.length - 1)) {
                sbValueColumns.append(",");
            }
        }
        final String valueColumns = sbValueColumns.toString();
        sb.append(") values(").append(valueColumns).append(")");
        String exesql = sb.toString();
        execSQLInternalWithTransaction(exesql);
    }

    @Override
    public final void saveAll(Collection<T> collection) {
        if (collection != null && collection.size() > 0) {
            Iterator<T> iterator = collection.iterator();
            boolean firstEnter = true;
            SQLiteDatabase writableDatabase = null;
            final String dbName = getTableName();
            final String insertHead = "INSERT INTO " + dbName + "(";
            final StringBuffer sb = new StringBuffer();
            final StringBuffer sbValueColumns = new StringBuffer();
            sb.append(insertHead);
            Class<T> clz = null;
            Field[] fields = null;
            try {
                writableDatabase = getWritableDatabase();
                writableDatabase.beginTransaction();
                while (iterator.hasNext()) {
                    T next = iterator.next();
                    if (firstEnter) {
                        firstEnter = false;
                        clz = (Class<T>) next.getClass();
                        fields = getDeclaredFieldsByFilter(clz, false);
                    }
                    for (int i = 0; i < fields.length; i++) {
                        //表字段的sql插入语句
                        Field field = fields[i];
                        String name = field.getName();
                        sb.append(name);
                        if (i != (fields.length - 1)) {
                            sb.append(",");
                        }
                        //合成values段的sql插入语句
                        field.setAccessible(true);
                        try {
                            Object o = field.get(next);
                            sbValueColumns.append("'" + o + "'");
                        } catch (IllegalAccessException e) {
                            e.printStackTrace();
                        }
                        if (i != (fields.length - 1)) {
                            sbValueColumns.append(",");
                        }
                    }
                    String valueColumns = sbValueColumns.toString();
                    sb.append(") values(").append(valueColumns).append(")");
                    String sql = sb.toString();
                    //执行sql语句
                    execSQLInternalWithoutTransaction(writableDatabase, sql);
                    sbValueColumns.delete(0, sbValueColumns.length());
                    sb.delete(insertHead.length(), sb.length());//删除所有的数据
                }
                writableDatabase.setTransactionSuccessful();
                writableDatabase.endTransaction();
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                if (writableDatabase != null) {
                    writableDatabase.close();
                }
            }
        }
    }

    @Override
    public final List<T> queryAll(Class<T> table) {
        //查出来
        String exesql = "SELECT * FROM " + getTableName();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = getReadableDatabase();
            cursor = rawQueryInternal(db, exesql, new String[0]);
            if (cursor == null || cursor.getCount() == 0) {
                return Collections.emptyList();
            }
            final List<T> list = new ArrayList<>();
            final Field[] fields = table.getDeclaredFields();
            while (cursor.moveToNext()) {
                T t = null;
                try {
                    t = table.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                    if (t == null) {
                        return Collections.emptyList();
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    if (t == null) {
                        return Collections.emptyList();
                    }
                }
                //每个字段都注入值了
                for (int i = 0; i < fields.length; i++) {
                    Field field = fields[i];
                    field.setAccessible(true);
                    String name = field.getName();
                    int columnIndex = cursor.getColumnIndex(name);
                    /**
                     * 子类实现接口
                     * {@link TableTypeFilter#getColumnType}
                     */
                    int type = getColumnType(columnIndex);
                    try {
                        switch (type) {
                            case TableTypeFilter.TYPE_INT:
                                final int intValue = cursor.getInt(columnIndex);
                                field.set(t, intValue);
                                break;
                            case TableTypeFilter.TYPE_FLOAT:
                                final float aFloat = cursor.getFloat(columnIndex);
                                field.set(t, aFloat);
                                break;
                            case TableTypeFilter.TYPE_LONG:
                                final long aLong = cursor.getLong(columnIndex);
                                field.set(t, aLong);
                                break;
                            case TableTypeFilter.TYPE_BLOB:
                                final byte[] blob = cursor.getBlob(columnIndex);
                                field.set(t, blob);
                                break;
                            case TableTypeFilter.TYPE_DOUBLE:
                                final double aDouble = cursor.getDouble(columnIndex);
                                field.set(t, aDouble);
                                break;
                            case TableTypeFilter.TYPE_SHORT:
                                final short aShort = cursor.getShort(columnIndex);
                                field.set(t, aShort);
                                break;
                            case TableTypeFilter.TYPE_STRING:
                                final String string = cursor.getString(columnIndex);
                                field.set(t, string);
                                break;
                            default:
                                break;
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                //这个对象的每个字段都注入值后，将其加入集合中
                list.add(t);
            }
            //while end
            return list;
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return Collections.emptyList();
    }

    @Override
    public final void update(T t) {
        //UPDATE COMPANY SET ADDRESS = 'Texas',age = 12 WHERE ID = 6;
        final Class<T> table = (Class<T>) t.getClass();
        final String tableName = getTableName();
//        final Field[] fields = foreignTableName.getDeclaredFields();
        final Field[] fields = getDeclaredFieldsByFilter(table, true);
        final StringBuffer sb = new StringBuffer();
        sb.append("UPDATE " + tableName + " SET ");
        final StringBuffer updateKeyValues = new StringBuffer();
        //每个字段都注入值了
        long id = 0;
        for (int i = 0; i < fields.length; i++) {
            try {
                Field field = fields[i];
                field.setAccessible(true);
                String name = field.getName();
                Object value;
                if (ID.equals(name)) {
                    value = id = field.getLong(t);
                } else {
                    value = field.get(t);
                }
                updateKeyValues.append(name + "='" + value + "'");
                if (i < (fields.length - 1)) {//非最后一个的时候都需要加一个逗号
                    updateKeyValues.append(",");
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        final String updateKeyValuesStr = updateKeyValues.toString();
        sb.append(updateKeyValuesStr);
        sb.append(" WHERE ");
        sb.append(ID + "=" + id);
        String sql = sb.toString();
        execSQLInternalWithTransaction(sql);
    }

    @Override
    public final List<T> queryAll(Class<T> table, String order) {
        //SELECT * FROM COMPANY ORDER BY SALARY ASC;ASC升序 DESC降序
        return null;
    }

    @Override
    public final List<T> queryAll(Class<T> table, String order, int limit) {
        //SELECT * FROM COMPANY LIMIT 6;
        return null;
    }

    @Override
    public final T queryById(Class<T> table, Object id) {
        return null;
    }

    /**
     *
     * @param table
     * @param useWhere
     * @param conditions 不需要加入 WHERE 关键字，只需要条件即可，必须是有占位符的，比如
     *                   AGE >= 25 AND SALARY >= 65000要写成，AGE >= ? AND SALARY >= ?
     * @param whereSelections
     * @param useOrder
     * @param isAsc
     * @param orderByColumnName
     * @param useLimit
     * @param limit
     * @return
     */
    @Override
    public final List<T> queryAllByOrderOrLimit(Class<T> table,
                                                boolean useWhere, String conditions, String[]
                                                   whereSelections,
                                                boolean useOrder, boolean isAsc, String
                                                   orderByColumnName,
                                                boolean useLimit, int limit) {
        //SELECT * FROM COMPANY ORDER BY SALARY ASC LIMIT 10;
        //SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000 ORDER BY SALARY ASC LIMIT 10;
        //查出来
        final String exesql = "SELECT * FROM " + getTableName();

        final StringBuffer sb = new StringBuffer();
        sb.append(exesql);
        if (useWhere) {
            sb.append(" WHERE ").append(conditions);
        }
        if (useOrder) {
            sb.append(" ORDER BY " + orderByColumnName);
            if (isAsc) {
                //升序 ASC
                sb.append(" ASC");
            } else {
                //降序 DESC
                sb.append(" DESC");
            }
        }
        if (useLimit) {
            sb.append(" LIMIT ").append(limit);
        }
        final String sql = sb.toString();

        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = getReadableDatabase();
            cursor = rawQueryInternal(db, sql, useWhere && whereSelections != null ?
                    whereSelections : new String[0]);
            if (cursor == null || cursor.getCount() == 0) {
                return Collections.emptyList();
            }
            final List<T> list = new ArrayList<>();
            final Field[] fields = table.getDeclaredFields();
            while (cursor.moveToNext()) {
                T t = null;
                try {
                    t = table.newInstance();
                } catch (InstantiationException e) {
                    e.printStackTrace();
                    if (t == null) {
                        cursor.close();
                        return Collections.emptyList();
                    }
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                    if (t == null) {
                        cursor.close();
                        return Collections.emptyList();
                    }
                }
                //每个字段都注入值了
                for (int i = 0; i < fields.length; i++) {
                    Field field = fields[i];
                    field.setAccessible(true);
                    String name = field.getName();
                    int columnIndex = cursor.getColumnIndex(name);
                    /**
                     * 子类实现接口
                     * {@link TableTypeFilter#getColumnType}
                     */
                    int type = getColumnType(columnIndex);
                    try {
                        switch (type) {
                            case TableTypeFilter.TYPE_INT:
                                final int intValue = cursor.getInt(columnIndex);
                                field.set(t, intValue);
                                break;
                            case TableTypeFilter.TYPE_FLOAT:
                                final float aFloat = cursor.getFloat(columnIndex);
                                field.set(t, aFloat);
                                break;
                            case TableTypeFilter.TYPE_LONG:
                                final long aLong = cursor.getLong(columnIndex);
                                field.set(t, aLong);
                                break;
                            case TableTypeFilter.TYPE_BLOB:
                                final byte[] blob = cursor.getBlob(columnIndex);
                                field.set(t, blob);
                                break;
                            case TableTypeFilter.TYPE_DOUBLE:
                                final double aDouble = cursor.getDouble(columnIndex);
                                field.set(t, aDouble);
                                break;
                            case TableTypeFilter.TYPE_SHORT:
                                final short aShort = cursor.getShort(columnIndex);
                                field.set(t, aShort);
                                break;
                            case TableTypeFilter.TYPE_STRING:
                                final String string = cursor.getString(columnIndex);
                                field.set(t, string);
                                break;
                            default:
                                break;
                        }
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
                //这个对象的每个字段都注入值后，将其加入集合中
                list.add(t);
            }
            //while end
            return list;
        } catch (SecurityException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            if (cursor != null) {
                cursor.close();
            }
            if (db != null) {
                db.close();
            }
        }
        return Collections.emptyList();
    }

    @Override
    public final void deleteById(Object id) {
        deleteByColumnAndValue(ID, id);
    }

    @Override
    public final void deleteByColumnAndValue(String columnName, Object value) {
        final String dbName = getTableName();
        StringBuffer sb = new StringBuffer();
        //DELETE FROM COMPANY WHERE ID = 7;
        sb.append("DELETE FROM ").append(dbName).append(" WHERE ").append(columnName)
                .append("='").append(value).append("'");
        String sql = sb.toString();
        execSQLInternalWithTransaction(sql);
    }

    @Override
    public final void clear() {
        final String dbName = getTableName();
        StringBuffer sb = new StringBuffer();
        //DELETE FROM COMPANY
        sb.append("DELETE FROM ").append(dbName);
        String sql = sb.toString();
        execSQLInternalWithTransaction(sql);
    }

}
